package cn.kgc.tangcco.tcbd1011.bigredbook.tbuser.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.kgc.tangcco.tcbd1011.bigredbook.pageresult.PageResult;
import cn.kgc.tangcco.tcbd1011.bigredbook.tbuser.dao.UserDao;
import cn.kgc.tangcco.tcbd1011.bigredbook.tbuser.entity.Tbuser;
import cn.kgc.tangcco.tcbd1011.bigredbook.util.Utils;
import cn.kgc.tangcco.tcbd1011.weihuihui.comm.util.JDBCUtil;

public class UserDaoImpl implements UserDao {

	private QueryRunner queryrunner;
	public UserDaoImpl() {
		queryrunner=new QueryRunner(JDBCUtil.getDataSource());
	}


	@Override
	public Tbuser login(Tbuser user) {
		String sql = "select * from tbuser where (username=? or telphone=? )";
		try {
			return this.queryrunner.query(sql, new BeanHandler<>(Tbuser.class), user.getAccout(),user.getAccout());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	
	@Override
	public int addUser(Tbuser user) {
		return Utils.executeUpdate("INSERT INTO tbuser VALUES (DEFAULT,?,?,?,?,?)",user.getUsername(),user.getPassword(),user.getUserroles(),user.getNickname(),user.getTelphone() );
	}

	//�޸ķ���
	@Override
	public int updateUser(Tbuser user) {
		return Utils.executeUpdate("UPDATE tbuser SET username=?,PASSWORD=?,userroles=?,nickname=?,telphone=? \r\n" + 
				"WHERE userid=?", user.getUsername(),user.getPassword(),user.getUserroles(),user.getNickname(),user.getTelphone(),user.getUserid());
	}

	//ɾ������
	@Override
	public int deleteUser(int pk) {
		return Utils.executeUpdate("DELETE FROM tbuser WHERE userid=?", pk);
	}



	//�鿴һ��
	@Override
	public Tbuser detailUser(int pk) {
		try {
			return this.queryrunner.query("select * from tbuser where userid=?", new BeanHandler<>(Tbuser.class), pk);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	
	@Override
	public int queryCountUser(Tbuser user) {
		String sql="select count(*) from tbuser where 1=1 ";
		StringBuffer  sb=new StringBuffer();
		List<Object> list=new ArrayList<>();
		sb.append(sql);
		if(user.getUsername()!=null && !user.getUsername().isEmpty()) {
			sb.append(" and username like ?");
			list.add("%"+user.getUsername()+"%");	
		}
		if(user.getUserroles()!=null && user.getUserroles()>0) {
			sb.append(" and userroles=?");
			list.add(user.getUserroles());
		}
		sql = sb.toString();
		try {
			System.out.println(((Long)this.queryrunner.query(sql, new ScalarHandler<>(),list.toArray())).intValue());
			return ((Long)this.queryrunner.query(sql, new ScalarHandler<>(),list.toArray())).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
		
	}

	@Override
	public List<Tbuser> queryAllUser(Tbuser user, PageResult pages) {
		String sql="select * from tbuser where 1=1";
		StringBuffer sb=new StringBuffer();
		List<Object> list=new ArrayList<>();
		sb.append(sql);
		if(user.getUsername()!=null && !user.getUsername().isEmpty()) {
			sb.append(" and username like ?");
			list.add("%"+user.getUsername()+"%");	
		}
		if(user.getUserroles()!=null && user.getUserroles()>0) {
			sb.append(" and userroles=?");
			list.add(user.getUserroles());
		}
		sb.append(" ORDER BY userid DESC");
		sb.append(" LIMIT ?,?");
		sql = sb.toString();
		list.add((pages.getPageNumber()-1)*pages.getPageSize());
		list.add(pages.getPageSize());
		try {
			return this.queryrunner.query(sql, new BeanListHandler<>(Tbuser.class), list.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
